create or replace
PROCEDURE SP_MIGRAR_CLIENTES(
                 p_programa_org IN OUT VARCHAR2,
                  p_programa_des IN OUT VARCHAR2,  
                  p_edad_ini IN OUT VARCHAR2,  
                  p_edad_fin IN OUT VARCHAR2,
                  p_fec_afili_ini IN  OUT VARCHAR2,  
                  p_fec_afili_fin IN  OUT VARCHAR2, 
                  p_departamento IN  OUT  varchar2,
                  p_provincia IN  OUT  varchar2,
                  p_distrito IN  OUT  varchar2,
                  p_desafiliar IN OUT  VARCHAR2,
                  p_co_usuario_modificador IN varchar2,  
                  deprogrmaorg out varchar2,
                  deprogrmades out varchar2,
                  distrito out varchar,
                  edad out char,
                  fec_afiliacion out char,
                  prg_destino out varchar2,
                  nro_desafiliados out number,
                  nro_afiliados out number,
                  p_deMensaje  out varchar2,
                  p_motivo  out varchar2,
                  rangosi in out varchar2,
                  rangono in out varchar2  


) AS 
idprogramaorg char(15);
nocompatible char(10);
conta number;
nrodesafi number;
nroafi number;
cliente varchar2(255);
my_code number;
my_errm varchar2(32767);
fechas varchar2(255);
edades varchar2(255);
nroclient number;
org number;
ubi varchar2(500);
nro number;
depa varchar2(255);
prov varchar2(255);
distri varchar2(255);
x number;
nc number;
contacli number;
valor number;
valor1 number;
canti number;
constante number;

---- cursor de clientes del programa origen que van a migrar 
cursor progorgi is
         SELECT
         
    cpe.id as cliente,
    p.de_programa as progrmaorigen,
    cp.id as idprogramaorg,
    cp.st_cliente_programa as estadoorg,
    cpe.da_fecha_inscripcion AS Fec_Inscripcion,
    cp.clpb_programa as idprograma
      
    FROM clpb_programa p
    JOIN clpr_cliente_programa cp
    ON p.id=cp.clpb_programa
    JOIN clpb_cliente_persona cpe
    ON cp.clpb_cliente_persona=cpe.id
    left outer join  clpm_ubigeo u
    ON u.id                     =cpe.co_ubigeo
    WHERE 
        cp.clpb_programa        =p_programa_org
    AND (cpe.co_ubigeo is null or  u.co_departamento = decode(NULL,null,u.co_departamento,NULL)  or  u.co_provincia = decode(NULL,null,u.co_provincia,NULL) or u.co_distrito = decode(NULL,null,u.co_distrito,NULL))
    AND (p_edad_ini <= to_number(to_char(sysdate,'YYYY'))- to_number(to_char(cpe.da_fecha_nacimiento,'YYYY')) or p_edad_ini is null )
    AND (p_edad_fin >= to_number(to_char(sysdate,'YYYY'))- to_number(to_char(cpe.da_fecha_nacimiento,'YYYY')) or p_edad_fin is null )
    AND (to_date(p_fec_afili_ini,'dd/MM/yyyy') <= to_date(to_char(cpe.da_fecha_inscripcion,'dd/MM/yyyy') ,'dd/MM/yyyy') or p_fec_afili_ini is null )
    AND (to_date(p_fec_afili_fin,'dd/MM/yyyy') >= to_date(to_char(cpe.da_fecha_inscripcion,'dd/MM/yyyy') ,'dd/MM/yyyy') or p_fec_afili_fin is null ) 
    AND cp.st_cliente_programa='Y'
    AND cpe.st_cliente_persona  ='Y';
   
   
-------------------------------------------------------------
cursor progdestino is
    SELECT
         
    cpe.id as cliente,
    p.de_programa as progrmaorigen,
    cp.id as idprogramaorg,
    cp.st_cliente_programa as estadoorg,
    cpe.da_fecha_inscripcion AS Fec_Inscripcion,
    cp.clpb_programa as idprograma
      
    FROM clpb_programa p
    JOIN clpr_cliente_programa cp
    ON p.id=cp.clpb_programa
    JOIN clpb_cliente_persona cpe
    ON cp.clpb_cliente_persona=cpe.id
    left outer join  clpm_ubigeo u
    ON u.id                     =cpe.co_ubigeo
    where 
        cp.clpb_programa        =p_programa_des
    AND (cpe.co_ubigeo is null or  u.co_departamento = decode(NULL,null,u.co_departamento,NULL)  or  u.co_provincia = decode(NULL,null,u.co_provincia,NULL) or u.co_distrito = decode(NULL,null,u.co_distrito,NULL))
    AND (p_edad_ini <= to_number(to_char(sysdate,'YYYY'))- to_number(to_char(cpe.da_fecha_nacimiento,'YYYY')) or p_edad_ini is null )
    AND (p_edad_fin >= to_number(to_char(sysdate,'YYYY'))- to_number(to_char(cpe.da_fecha_nacimiento,'YYYY')) or p_edad_fin is null )
    AND (to_date(p_fec_afili_ini,'dd/MM/yyyy') <= to_date(to_char(cpe.da_fecha_inscripcion,'dd/MM/yyyy') ,'dd/MM/yyyy') or p_fec_afili_ini is null )
    AND (to_date(p_fec_afili_fin,'dd/MM/yyyy') >= to_date(to_char(cpe.da_fecha_inscripcion,'dd/MM/yyyy') ,'dd/MM/yyyy') or p_fec_afili_fin is null ) 
    and cp.st_cliente_programa='Y'
    AND cpe.st_cliente_persona  ='Y';

-------------------------------------------------------------

cursor nocomp is
   select 
           nc.co_programa_no_compatible as idnoprogra, 
           clp.clpb_cliente_persona as idclientepro,
           cp.de_programa as deprogramanocomp
           
      from  clpr_cliente_programa clp,clpr_no_compatibilidad nc 
            left join  clpb_programa cp
            on cp.id= nc.co_programa_no_compatible
    where nc.co_programa=p_programa_des and clp.clpb_programa=nc.co_programa_no_compatible;
      
      
----------------------------------------------------------------------
 ---- cursor de clientes del programa destino que van a migrar 
cursor progdes is

    SELECT
         
    cpe.id as cliente,
    p.de_programa as progrmadestino,
    cp.id as idprogramades,
    cp.st_cliente_programa as estadoorg,
    cpe.da_fecha_inscripcion AS Fec_Inscripcion,
    cp.clpb_programa as idprograma 
    FROM clpb_programa p
    JOIN clpr_cliente_programa cp
    ON p.id=cp.clpb_programa
    JOIN clpb_cliente_persona cpe
    ON cp.clpb_cliente_persona=cpe.id
    left join  clpm_ubigeo u
    ON u.id                     =cpe.co_ubigeo
    WHERE cp.clpb_programa        =p_programa_des
    AND  cp.st_cliente_programa='Y'
    AND cpe.st_cliente_persona  ='Y';
---------------------------------------------------------------------

BEGIN

  p_deMensaje := '';
  p_motivo :='';
  rangosi:='';
  rangono:='';
  conta:=0; 
  nro:=0;
  nrodesafi:=0;
  nroafi:=0;
  nroclient:=0;
  org :=0;
  ubi:='';
  deprogrmades:='';
  deprogrmaorg:='';
  depa:='';
  prov:='';
  distri:='';
  valor:=0;
  valor1:=0;
  x:=0;
  nc:=0;
  contacli:=0;
  canti :=0;
  constante :=0;
 
  --- valido el ingreso de departamento     

  
  --- saber si son compatibles o imcompatibles los programas
  select  
      count(n.id) into conta
  from clpr_no_compatibilidad n 
            join clpb_programa p1 on n.co_programa = p1.id 
            join clpb_programa p2 on n.co_programa_no_compatible = p2.id 
  where n.st_nocompatibilidad = 'Y' 
            and n.co_programa = p_programa_org
            and n.co_programa_no_compatible = p_programa_des;
            
            
 -------------------------------------------------------------------------------------
 
    SELECT    
    count(cp.id) into canti
    FROM clpb_programa p
    JOIN clpr_cliente_programa cp
    ON p.id=cp.clpb_programa
    JOIN clpb_cliente_persona cpe
    ON cp.clpb_cliente_persona=cpe.id
    left outer join  clpm_ubigeo u
    ON u.id                     =cpe.co_ubigeo
    where 
        cp.clpb_programa        =p_programa_des
    AND (cpe.co_ubigeo is null or  u.co_departamento = decode(NULL,null,u.co_departamento,NULL)  or  u.co_provincia = decode(NULL,null,u.co_provincia,NULL) or u.co_distrito = decode(NULL,null,u.co_distrito,NULL))
    AND (p_edad_ini <= to_number(to_char(sysdate,'YYYY'))- to_number(to_char(cpe.da_fecha_nacimiento,'YYYY')) or p_edad_ini is null )
    AND (p_edad_fin >= to_number(to_char(sysdate,'YYYY'))- to_number(to_char(cpe.da_fecha_nacimiento,'YYYY')) or p_edad_fin is null )
    AND (to_date(p_fec_afili_ini,'dd/MM/yyyy') <= to_date(to_char(cpe.da_fecha_inscripcion,'dd/MM/yyyy') ,'dd/MM/yyyy') or p_fec_afili_ini is null )
    AND (to_date(p_fec_afili_fin,'dd/MM/yyyy') >= to_date(to_char(cpe.da_fecha_inscripcion,'dd/MM/yyyy') ,'dd/MM/yyyy') or p_fec_afili_fin is null ) 
    and cp.st_cliente_programa='Y'
    and cpe.st_cliente_persona  ='Y';

 --------------------------------------------------------------------------------
   ----- saber si hay clientes incompatibles
       select count(clp.clpb_cliente_persona) into nc
        from clpr_no_compatibilidad nc,clpr_cliente_programa clp
      where nc.co_programa=p_programa_des and clp.clpb_programa=nc.co_programa_no_compatible;
  ---- si hay clientes  ----------------------------------------------------------- 

  SELECT
         count(cp.id) into x
    FROM clpb_programa p
    JOIN clpr_cliente_programa cp
    ON p.id=cp.clpb_programa
    JOIN clpb_cliente_persona cpe
    ON cp.clpb_cliente_persona=cpe.id
    JOIN clpm_ubigeo u
    ON u.id                     =cpe.co_ubigeo
    WHERE  cp.clpb_programa        =p_programa_org
    AND cp.st_cliente_programa='Y'
    AND cpe.st_cliente_persona  ='Y';
            
            
  --- se si mi origen tiene clientes activos
  
  
  Select 
        count(st.id) into org 
  from clpr_cliente_programa  st
  where st.st_cliente_programa='Y'
       and st.clpb_programa=p_programa_org;
  
  ---Saco la descripcion del programa destino
    select de_programa into deprogrmades
    from clpb_programa where id=p_programa_des;
    
    select de_programa into deprogrmaorg
    from clpb_programa where id=p_programa_org;


         if(p_departamento is not null and p_provincia is null and p_distrito  is null) then  
             Select  u.de_departamento into depa
             from clpm_ubigeo u where u.co_departamento = p_departamento  
             group by  u.de_departamento;
             ubi:=depa;        
          elsif(p_departamento is not null and p_provincia is not null and p_distrito  is null)then
             Select u.de_departamento,u.de_provincia  into depa, prov
            from clpm_ubigeo u where u.co_departamento = p_departamento  and u.co_provincia=p_provincia
            group by  u.de_departamento,  u.de_provincia;
             ubi:=depa ||' '||prov;
             
          elsif(p_departamento is not null  and p_provincia is not null and p_distrito is not null)then
            Select    u.de_departamento,u.de_provincia,u.de_distrito  into depa, prov ,distri
                       
            from clpm_ubigeo u where u.co_departamento = p_departamento  and u.co_provincia=p_provincia
            and u.de_distrito=p_distrito
            group by  u.de_departamento,  u.de_provincia, u.de_distrito;
                  ubi:=depa ||' '||prov ||' '|| distri;
          elsif(p_departamento is null and  p_provincia is null and p_distrito is  null)then 
                    ubi:='No Escogio Ubicacion';
          end if;
          distrito:=ubi;
  
  
---- son compatibles incio del IF1---

    if(p_desafiliar='Y')then
-- si el origen tiene clientes--    
      if(x <>0)then
     
         for clientesorigen in progorgi loop
         --progdestino
         
            for clientesdestino in progdes loop
             if(clientesorigen.cliente=clientesdestino.cliente)then
             valor:=clientesorigen.cliente;
              --los que son iguales
               nrodesafi:=nrodesafi +1;   

               if(clientesdestino.estadoorg='N') then
               rangosi:=rangosi||clientesorigen.cliente||','; 
                    update clpr_cliente_programa cp
                            SET cp.ST_CLIENTE_PROGRAMA='Y', cp.DA_FECHA_MODIFICACION = sysdate,cp.co_usuario_modificador= p_co_usuario_modificador
                     where cp.id=clientesorigen.idprogramaorg; 
            
                    update clpr_cliente_programa cp
                            SET cp.ST_CLIENTE_PROGRAMA='N', cp.DA_FECHA_MODIFICACION = sysdate,cp.co_usuario_modificador= p_co_usuario_modificador
                     where cp.id=clientesorigen.idprogramaorg;
                     
                elsif (clientesdestino.estadoorg='Y')then  
            
                    update clpr_cliente_programa cp
                            SET cp.ST_CLIENTE_PROGRAMA='N', cp.DA_FECHA_MODIFICACION = sysdate,cp.co_usuario_modificador= p_co_usuario_modificador
                     where cp.id=clientesorigen.idprogramaorg;

                   end if;
                end if;
           end loop;
           
              for clientesdestino in progdestino loop
              
              if(clientesorigen.cliente=clientesdestino.cliente) then
                  rangosi:=rangosi;
                  constante:=0;
                  exit;
               else
               constante:=1;
               end if;

              end loop;
           
              if(canti=0)then

                 if(clientesorigen.cliente<>valor and clientesorigen.cliente<>valor1)then
                  nroafi := nroafi +1; 
                  rangosi:=rangosi||clientesorigen.cliente||',';
                  valor1:=clientesorigen.cliente;
                  insert into clpr_cliente_programa(
                          id, co_usuario_creador,co_usuario_modificador,da_fecha_creacion,da_fecha_modificacion, st_cliente_programa,clpb_cliente_persona, clpb_programa)
                  values(
                      seq_cliente_programa.nextval, p_co_usuario_modificador,null,sysdate,null,'Y', clientesorigen.cliente, p_programa_des);
                  end if; 
              end if;
              
              if(constante =1)then
                if(clientesorigen.cliente<>valor and clientesorigen.cliente<>valor1)then
                  nroafi := nroafi +1; 
                  rangosi:=rangosi||clientesorigen.cliente||',';
                  valor1:=clientesorigen.cliente;
                  
                  insert into clpr_cliente_programa(
                              id, co_usuario_creador,co_usuario_modificador,da_fecha_creacion,da_fecha_modificacion, st_cliente_programa,clpb_cliente_persona, clpb_programa)
                           values(
                               seq_cliente_programa.nextVal, p_co_usuario_modificador,null,sysdate,null,'Y', clientesorigen.cliente, p_programa_des);   
              
                  update clpr_cliente_programa cp
                                SET cp.ST_CLIENTE_PROGRAMA='N', cp.DA_FECHA_MODIFICACION = sysdate, cp.co_usuario_modificador= p_co_usuario_modificador
                   where cp.id=clientesorigen.idprogramaorg ;
                   
              end if; 
            end if;
    
         end loop;
       p_deMensaje := p_deMensaje ||'EXITO';
      else
      p_deMensaje := p_deMensaje ||'ERROR';
      end if;
      
    else
    
    -- si el origen tiene clientes--    
      if(x <>0)then
     
    for clientesorigen in progorgi loop

                for ncom in nocomp loop
          
                    if(clientesorigen.cliente=ncom.idclientepro AND p_desafiliar='N')then
                        rangono :=rangono||ncom.idclientepro||',';
                        valor := ncom.idclientepro;
                        p_deMensaje :='VACIO_PROGRAMAORG';
                        p_motivo := p_motivo ||ncom.deprogramanocomp||',';
                        exit;
                     end if;
                  end loop;
                  

              for client in(
                    select cp.id as cliente_programa, 
                    cp.st_cliente_programa as estado,
                    cp.clpb_cliente_persona as idcliente,
                    cp.clpb_programa as idprograma
                    from clpr_cliente_programa cp
                    where cp.clpb_programa=p_programa_des and cp.clpb_cliente_persona=clientesorigen.cliente)loop  
                  
              
                  nroclient:= nroclient +1;
                  if(nroclient>0 and client.estado='N')then
                  nroafi := nroafi +1; 
                  rangosi:=rangosi||clientesorigen.cliente||',';
                  update clpr_cliente_programa cp
                            SET cp.st_cliente_programa='Y', cp.da_fecha_modificacion = sysdate,cp.co_usuario_modificador= p_co_usuario_modificador
                         where cp.id=client.cliente_programa;
                  end if;
                  
              end loop; 
              
              
            for clientesdestino in progdestino loop
              
              if(clientesorigen.cliente=clientesdestino.cliente) then
                  rangosi:=rangosi;
                  constante:=0;
                  exit;
               else
               constante:=1;
               end if;

              end loop;
              
              if(canti=0)then

                 if(clientesorigen.cliente<>valor and clientesorigen.cliente<>valor1)then
                  nroafi := nroafi +1; 
                  rangosi:=rangosi||clientesorigen.cliente||',';
                  valor1:=clientesorigen.cliente;
                  insert into clpr_cliente_programa(
                          id, co_usuario_creador,co_usuario_modificador,da_fecha_creacion,da_fecha_modificacion, st_cliente_programa,clpb_cliente_persona, clpb_programa)
                  values(
                      seq_cliente_programa.nextval, p_co_usuario_modificador,null,sysdate,null,'Y', clientesorigen.cliente, p_programa_des);
                  end if; 
              end if;
              
              if(constante =1)then
                if(clientesorigen.cliente<>valor and clientesorigen.cliente<>valor1)then
                  nroafi := nroafi +1; 
                  rangosi:=rangosi||clientesorigen.cliente||',';
                  valor1:=clientesorigen.cliente;
                  insert into clpr_cliente_programa(
                          id, co_usuario_creador,co_usuario_modificador,da_fecha_creacion,da_fecha_modificacion, st_cliente_programa,clpb_cliente_persona, clpb_programa)
                  values(
                      seq_cliente_programa.nextval, p_co_usuario_modificador,null,sysdate,null,'Y', clientesorigen.cliente, p_programa_des);
                  end if; 
              end if; 
              
                   
                  
            
      end loop;
     end if;
     
    p_deMensaje := p_deMensaje ||'EXITO';
   
    end if;  
  nro_afiliados:=nroafi;
  nro_desafiliados:=nrodesafi;
  commit;
   
   exception
       when  others then
       begin
              
          my_code := sqlcode;
          my_errm := sqlerrm;
          dbms_output.put_line('Error code ' || my_code || ': ' || my_errm);
          rollback;
          
       end;
END SP_MIGRAR_CLIENTES;